﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FOModels.BlogNews;
namespace DataAccess.BlogNews
{
    public class BlogNewsCategoryDAL
    {
        private SQLDataAccess db;
        public BlogNewsCategoryDAL()
        {
            db = new SQLDataAccess();
        }
        // Lấy top 3 loại tin có tin nhiều nhất
        public DataTable gettop3catebynews(int langid)
        {
            return db.exeSelect("select top 3 b.BlogNewsCategoryID,bl.BlogNewsCategoryName from BlogNewsCategory b join BlogNewsCategoryLang bl on b.BlogNewsCategoryID=bl.BlogNewsCategoryID join BlogNews bn on b.BlogNewsCategoryID = bn.BlogNewsCategoryID where bn.Enable =1 and bl.LangID = " + langid + " and b.ParentID is not null group by b.BlogNewsCategoryID,bl.BlogNewsCategoryName order by COUNT(bl.BlogNewsCategoryName)");
        }
        // Lấy danh sách blog news cate trang quản lý
        public DataTable getallBlogNewsCategory(int langid)
        {
            return db.exeSelect("select b.BlogNewsCategoryID,b.ParentID,b.Enable,bl.BlogNewsCategoryName from dbo.BlogNewsCategory b join dbo.BlogNewsCategoryLang bl on b.BlogNewsCategoryID = bl.BlogNewsCategoryID where bl.LangID= " + langid + " order by BlogNewsCategoryID ");
        }
        // Lấy danh sách blog cate is null and Enbale = 1
        public DataTable ResposiBlogNewsCategoryIsNullEnable(int langid)
        {
            return db.exeSelect("select b.BlogNewsCategoryID,b.ParentID,b.Enable,bl.BlogNewsCategoryName from dbo.BlogNewsCategory b join dbo.BlogNewsCategoryLang bl on b.BlogNewsCategoryID = bl.BlogNewsCategoryID where b.Enable = 1 and bl.LangID = " + langid + " and ParentID is null");
        }
        // Lấy danh sách blog news cate trang quản lý
        public DataTable ResposiBlogNewsCategory(int langid)
        {
            return db.exeSelect("select b.BlogNewsCategoryID,b.ParentID,b.Enable,bl.BlogNewsCategoryName, la.FlagImage from dbo.BlogNewsCategory b join dbo.BlogNewsCategoryLang bl on b.BlogNewsCategoryID = bl.BlogNewsCategoryID join Language la on bl.LangID = la.ID where la.ID = " + langid + " and bl.LangID = " + langid + " and ParentID is null");
        }
        // Lấy danh sách blog news cate trang quản lý, them , sua
        public DataTable ResposiBlogNewsCategoryManager(int langid)
        {
            return db.exeSelect("select b.BlogNewsCategoryID,b.ParentID,b.Enable,bl.BlogNewsCategoryName, la.FlagImage from dbo.BlogNewsCategory b join dbo.BlogNewsCategoryLang bl on b.BlogNewsCategoryID = bl.BlogNewsCategoryID join Language la on bl.LangID = la.ID where la.ID = " + langid + " and bl.LangID = " + langid + " and b.ParentID is not null");
        }
        // Lấy danh sách blog news cate de xuat ra giao dien enbale =1
        public DataTable ResposiBlogNewsCategoryManagerExViewByCate(int BlogNewsCategoryID, int langid)
        {
            return db.exeSelect("select b.BlogNewsCategoryID,b.ParentID,b.Enable,bl.BlogNewsCategoryName from dbo.BlogNewsCategory b join dbo.BlogNewsCategoryLang bl on b.BlogNewsCategoryID = bl.BlogNewsCategoryID  where b.Enable =1 and b.ParentID = " + BlogNewsCategoryID + " and bl.LangID = " + langid + " and b.ParentID is not null");
        }
        // Lấy danh sách blog news cate de xuat ra giao dien enbale =1
        public DataTable ResposiBlogNewsCategoryManagerExView(int langid)
        {
            return db.exeSelect("select b.BlogNewsCategoryID,b.ParentID,b.Enable,bl.BlogNewsCategoryName from dbo.BlogNewsCategory b join dbo.BlogNewsCategoryLang bl on b.BlogNewsCategoryID = bl.BlogNewsCategoryID  where b.Enable = 1 and bl.LangID = " + langid + " and b.ParentID is not null");
        }
        // Lấy thông tin news cate
        public DataTable getBlogNewsCategoryInfo(int parentid, int langid)
        {
            return db.exeSelect("select b.BlogNewsCategoryID,b.ParentID,b.Enable,bl.BlogNewsCategoryName from dbo.BlogNewsCategory b join dbo.BlogNewsCategoryLang bl on b.BlogNewsCategoryID = bl.BlogNewsCategoryID where b.BlogNewsCategoryID= " + parentid + "  and bl.LangID=  " + langid + "");
        }

        // Lấy blog news cate mới nhất
        public DataTable GetTop1BlogNewsCategory()
        {
            return db.exeSelect("Select Top 1 BlogNewsCategoryID from BlogNewsCategory order by BlogNewsCategoryID desc");
        }

        // sửa blog news cate
        public int UpdateBlogNewsCategory(BlogNewsCategoryModels models)
        {
            return db.exeUpdate("update BlogNewsCategory set Enable = '" + models.Enable + "' , ParentID = '" + models.ParentID + "', BlogNewsCategoryName = N'" + models.BlogNewsCategoryName + "' where BlogNewsCategoryID = " + models.BlogNewsCategoryID + "");
        }

        public int UpdateBlogNewsCategoryLang(BlogNewsCategoryModels models, int langid)
        {
            return db.exeUpdate("update BlogNewsCategoryLang set BlogNewsCategoryName = N'" + models.BlogNewsCategoryName + "'where BlogNewsCategoryID = " + models.BlogNewsCategoryID + " and LangID=" + langid + "");
        }
        // Thêm blog news cate
        public int InsertBlogNewsCategory(BlogNewsCategoryModels models)
        {
            return db.exeUpdate("insert into BlogNewsCategory(ParentID,BlogNewsCategoryName) values (" + models.ParentID + ",N'" + models.BlogNewsCategoryName + "')");
        }
        public int InsertBlogNewsCategoryLang(BlogNewsCategoryModels models, int langid)
        {
            return db.exeUpdate("insert into BlogNewsCategoryLang values (" + models.BlogNewsCategoryID + "," + langid + ",N'" + models.BlogNewsCategoryName + "')");
        }
        // xóa blog news cate
        public int DeleteBlogNewsCategory(int BlogNewsCategoryID)
        {
            return db.exeUpdate("Delete from BlogNewsCategory where BlogNewsCategoryID = " + BlogNewsCategoryID + "");
        }
        public int DeleteBlogNewsCategoryLang(int BlogNewsCategoryID)
        {
            return db.exeUpdate("Delete from BlogNewsCategoryLang where BlogNewsCategoryID = " + BlogNewsCategoryID + "");
        }
    }
}
